﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLHelper;
using System.Data;
using Bill.BasDLL.ety;

namespace Bill.BasDLL.dao
{
    public class daoOraCodeRef
    {
        SQLHelp _sqlHelp = new SQLHelp();

        public daoOraCodeRef(SQLHelp iSqlHelp)
        {
            _sqlHelp = iSqlHelp;
        }
        /// <summary>
        /// 查找出所有代码的组别 czm 20121008
        /// </summary>
        /// <returns></returns>
        public DataTable GetCodeRefGrp()
        {
            string sql = " select ITEM,max(ITEM_DESC) as ITEM_DESC from code_ref group by item order by item ";
            string[] param = new string[] { };
            DataTable dtCodeRefGrp;
            return dtCodeRefGrp = _sqlHelp.ExecuteReader(sql, param);
        }
        /// <summary>
        /// 查找出所有代码
        /// </summary>
        /// <returns></returns>
        public DataTable GetCodeRefCode()
        {
            string sql = " select CODE,max(CODE_DESC) as CODE_DESC from code_ref group by CODE ";
            string[] param = new string[] { };
            DataTable dtCodeRefCode;
            return dtCodeRefCode = _sqlHelp.ExecuteReader(sql, param);
        }
        public DataTable GetCodeRef(string item)
        {
            //string sql = "select ITEM,ITEM_DESC,CODE,CODE_DESC,VALUE,AVAIL_IND,REMARK,to_char(substr(CODE_GRP,0,3000)) code_grp1,to_char(substr(CODE_GRP,3001)) code_grp2,result_ind from CODE_REF ";
            string sql = "select ITEM,ITEM_DESC,CODE,CODE_DESC,VALUE,AVAIL_IND,REMARK,DBMS_LOB.substr(code_grp,4000,1) code_grp1,DBMS_LOB.substr(code_grp,4000,4001) code_grp2,DBMS_LOB.substr(code_grp,4000,8001) code_grp3,DBMS_LOB.substr(code_grp,4000,12001) code_grp4,DBMS_LOB.substr(code_grp,4000,16001) code_grp5,result_ind,print_grp from CODE_REF ";
            string[] param;
            if (item == "**")
            {
                sql += " order by item,code ";
                param = new string[] { };
            }
            else
            {
                sql += " where item=:item order by item,code ";
                param = new string[] { item };
            }
            DataTable dtCodeRef;
            return dtCodeRef = _sqlHelp.ExecuteReader(sql, param);
        }

        public bool CheckExistCodeRef(etyCodeRef ieyCodeRef)
        {
            string sql = "select count(*) CNT from CODE_REF where item=:ITEM and code=:CODE and value=:VALUE";
            //string[] param = new string[] { 
            //ieyCodeRef.ITEM,
            //ieyCodeRef.CODE,
            //ieyCodeRef.VALUE
            //};
            IList<etyCodeRef> ilCodeRef = new List<etyCodeRef>();
            ilCodeRef.Add(ieyCodeRef);
            DataTable rst = _sqlHelp.ExecuteReader(sql, ilCodeRef);
            if (rst.Rows[0][0].ToString() == "0")
                return false;
            else
                return true;
        }
        public bool SaveCodeRef(etyCodeRef ieyCodeRef)
        {
            string sql = "insert into code_ref(item,item_desc,code,code_desc,value,remark,lst_upd_date,CODE_GRP,print_grp) "
                + " values(:ITEM,:ITEM_DESC,:CODE,:CODE_DESC,:VALUE,:REMARK,sysdate,:code_grp,:print_grp)";

            IList<etyCodeRef> ilCodeRef = new List<etyCodeRef>();
            ilCodeRef.Add(ieyCodeRef);
            daoResult rst = _sqlHelp.Execute<etyCodeRef>(sql, ilCodeRef);
            return rst.Exec_Success;
        }
        public bool UpdateCodeRef(etyCodeRef newCodeRef, etyCodeRef oldCodeRef)
        {
            ////string sql3 = " begin :RESULT:=GT_MANAGER.CodeRef_Process2(:ITEM,:ITEM_DESC,:CODE,:CODE_DESC:VALUE,:AVAIL_IND,:CODE_GRP,:oITEM,:oCODE,:oVALUE);end; ";
            //string sql3 = " begin :RESULT:=GT_MANAGER.CodeRef_Process2(:ITEM,:ITEM_DESC,:CODE,:CODE_DESC,:VALUE,:AVAIL_IND,:CODE_GRP,:oITEM,:oCODE,:oVALUE);end; ";
            //_sqlHelp.BeginTrans();
            //string[] param3 = new string[] { "      ",
            //newCodeRef.ITEM,
            //newCodeRef.ITEM_DESC,
            //newCodeRef.CODE,
            //newCodeRef.CODE_DESC,
            //newCodeRef.VALUE,
            //newCodeRef.AVAIL_IND,
            //newCodeRef.CODE_GRP
            //,oldCodeRef.ITEM,
            //oldCodeRef.CODE,
            //oldCodeRef.VALUE};
            //daoResult rst3 = _sqlHelp.ExecuteSQL(sql3, param3);
            //if (!rst3.Exec_Success)
            //{
            //    _sqlHelp.Rollback();
            //    return false;
            //}
            //_sqlHelp.Commit();
            //return true;

            string sql2 = " begin :RESULT:=GT_MANAGER.CodeRef_Process(:ITEM,:ITEM_DESC,:CODE,:CODE_DESC,:VALUE,:AVAIL_IND,:REMARK,sysdate,:CODE_GRP,:RESULT_IND,:oITEM,:oCODE,:oVALUE,:Print_Grp);end; ";
            _sqlHelp.BeginTrans();
            string[] param2 = new string[] { 
            "                                 ",
            newCodeRef.ITEM,
            newCodeRef.ITEM_DESC,
            newCodeRef.CODE,
            newCodeRef.CODE_DESC,
            newCodeRef.VALUE,
            newCodeRef.AVAIL_IND,
            newCodeRef.REMARK,
            newCodeRef.CODE_GRP,
            newCodeRef.RESULT_IND,
            oldCodeRef.ITEM,
            oldCodeRef.CODE,
            oldCodeRef.VALUE,
            newCodeRef.PRINT_GRP
            };
            daoResult rst2 = _sqlHelp.ExecuteSQL(sql2, param2);
            if (!rst2.Exec_Success)
            {
                _sqlHelp.Rollback();
                return false;
            }
            _sqlHelp.Commit();
            return true;

            //string sql = "update code_ref set item=:item,item_desc=:item_desc,code=:code,code_desc=:code_desc,value=:value,remark=:remark,lst_upd_date=sysdate,code_grp=:code_grp "
            //    + "where ITEM=:oITEM and CODE=:oCODE and VALUE=:oVALUE ";
            //string[] param = new string[] { 
            //newCodeRef.ITEM,
            //newCodeRef.ITEM_DESC,
            //newCodeRef.CODE,
            //newCodeRef.CODE_DESC,
            //newCodeRef.VALUE,
            //newCodeRef.REMARK,
            //newCodeRef.CODE_GRP,
            //oldCodeRef.ITEM,
            //oldCodeRef.CODE,
            //oldCodeRef.VALUE
            //};
            //_sqlHelp.BeginTrans();
            //daoResult rst = _sqlHelp.ExecuteSQL(sql, param);
            //_sqlHelp.Commit();
            //_sqlHelp.EndTrans();
            //return rst.Exec_Success;
        }
        public bool DeleteCodeRef(etyCodeRef delCodeRef)
        {
            string sql = "delete code_ref where ITEM=:ITEM and CODE=:CODE and VALUE=:VALUE ";

            IList<etyCodeRef> ilCodeRef=new List<etyCodeRef>();
            ilCodeRef.Add(delCodeRef);
            daoResult rst = _sqlHelp.Execute<etyCodeRef>(sql, ilCodeRef);
            return rst.Exec_Success;
        }
    }
}
